<?php
	class MssqlGenerator extends CodeGeneratorController
	{
		/* STORED PROCEDURES' GENERATION */
		
		/**
		 * seeks primary key field in a table fields
		 *
		 * @param array $data necessary data
		 * @return string $key_field
		 */
		function FindKeyField($data)
		{
			foreach ($data as $value)
			{
				if ($value[primary_key] == true)	
				{	
					$key_field[data_type] = $value[original_data_type];
					$key_field[name] = $value[column_name];
				}	
			}	
			return $key_field;
		}
				
		/**
		 * seeks table_name
		 *
		 * @param array $data necessary data
		 * @return string $table_name
		 */
		function FindTableName($data)
		{
			foreach ($data as $value)
			{
				if ($value[primary_key] == true)	
					$table_name = $value[table_name];
			}	
			return $table_name;
		}
		
		/**
		 * getting the root of the procedure name from a table name
		 *
		 * @param string $stored_procedure_prefix means usually 'sp_', we should add it in the beginning of the proc_name
		 * @param string $table_prefix means usually 't_', we should discard it
		 * @param string $table_name table name
		 * @param string $procedure_action_postfix means predestination of procedure
		 * @return string $procedure_name
		 */
		function FindProcedureName($stored_procedure_prefix, $table_prefix, $table_name, $procedure_action_postfix)
		{
			$procedure_body_name = eregi_replace("^$table_prefix", '', $table_name);
			$procedure_name = $stored_procedure_prefix.$procedure_body_name.$procedure_action_postfix;
			return $procedure_name;
		}

		/**
		 * getting the 'sort by' field: seeking for a field which ends with '_name' (by default) and root is as in key field
		 * if search is failed, we send key field name
		 *
		 * @param string $data necessary data
		 * @param string $key_field_name means key field name
		 * @param object $var_hull for getting some string constants
		 * @return string $value[column_name] | $key_field_name
		 */
		function FindSortByField($data, $key_field_name, $var_hull)
		{
			$root_field_name = trim(String::RemoveLastSubstring($key_field_name, $var_hull->keyFieldPostfix));	
			
			foreach ($data as $value)
			{
				if ($value[column_name] == ($root_field_name.$var_hull->nameFieldPostfix))
					return $value[column_name];
			}	
			return $key_field_name;
		}		
		
		/**
		 * here we check out if the table has field to mark deleted rows
		 *
		 * @param string $data necessary data
		 * @param object $var_hull for getting some string constants
		 * @return boolean if there is deletinf field - return true
		 */
		function HasDeletedField($data, $var_hull)
		{			
			foreach ($data as $value)
			{
				if ($value[column_name] == $var_hull->deletingFieldName)
					return true;
			}	
			return false;
		}		
		
		/**
		 * here we find out all tables, which is mentioned in $data
		 *
		 * @param string $data necessary data
		 * @return string all tables in the string with commas
		 */
		function AllTables($data)
		{			
			foreach ($data as $value)
			{
				$columns[] = $value[table_name];
			}	
			$columns = array_unique($columns);
			$columns = implode(', ', $columns);
			return $columns;
		}	
		
		/**
		 * here we find out all tables relations
		 *
		 * @param string $data necessary data
		 * @return string all tables are now intertwined
		 */
		function TablesRelations($data)
		{						
			foreach ($data as $value)
			{
				if (!empty($value[foreign_table]))
				{
					$relationships_string .= "AND $value[table_name].$value[column_name] = $value[foreign_table].$value[column_name]\n\t";		
				}
            }
            return $relationships_string;
		}	
		
		/**
		 * here we find out all fields in the table
		 *
		 * @param string $data necessary data
		 * @param string $indent for goodlooking coding
		 * @return string all fields with table_names separated by commas
		 */
		function TablesFields($data, $indent = '')
		{									
            foreach ($data as $value)
			{
				$fields_list .= "$value[table_name].$value[column_name],\n\t$indent";
			}		
				
			$fields_list =  trim(String::RemoveLastComma($fields_list));
			return $fields_list;
		}	
		
		/**
		 * gets 'Generated by' section
		 *
		 * @return string about generating of the code
		 */
		function GeneratedBy()
		{						
			$date = date("d/m/Y H:i");
			$generated = "Generated by SpherusPHPCodegenerator on ".$date;           
			return $generated;
		}	
		
		/**
		 * Generates stored procedure to insert data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureInsert($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_insert');		

			//creating description of variables, lists of fields, list of variables for insert
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName) && $value[primary_key] != true)
				{
					//variable name and type
					$variables_description .= "@{$value[column_name]} {$value[original_data_type]}";
					
					//variable length
					if (!empty($value[max_length]) && $value[original_data_type] != 'text') 
						$variables_description .= "({$value[max_length]})";
					
					//default value
					if ($value[default_value] != '')
					{
						$variables_description .= " = '{$value[default_value]}'";
					}
					else if ($value['is_null'] == 'YES')
					{
						$variables_description .= " = NULL";
					}
						
					$variables_description .= ",\n\t";				
					
					//fields list
					$fields_list .= "{$value[column_name]},\n\t";
					
					//variables list
					$variables_list .= "@{$value[column_name]},\n\t";
				}	
			}			
			
			//removing last comma in the lists of variables and fields
			$variables_description =  trim(String::RemoveLastComma($variables_description));			
			$fields_list =  trim(String::RemoveLastComma($fields_list));	
			$variables_list =  trim(String::RemoveLastComma($variables_list));	
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();

//making the whole string of procedure
$procedure_string =  <<<EOS_insert
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	$variables_description
)
AS
BEGIN
INSERT INTO $primary_table_name
(
	$fields_list
)
VALUES
(
	$variables_list
)
END

EOS_insert;
			return $procedure_string;
		}
		
		/**
		 * Generates stored procedure to update data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureUpdate($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_update');		

			//findung key field
			$key_field = $this->FindKeyField($data);
			
			//creating description of variables
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName))
				{
					$variables_description .= "@{$value[column_name]} {$value[original_data_type]}";
					if (!empty($value[max_length]) && $value[original_data_type] != 'text') 
						$variables_description .= "({$value[max_length]})";
						
					//default value
					if ($value[default_value] != '')
					{
						$variables_description .= " = '{$value[default_value]}'";
					}
					else if ($value['is_null'] == 'YES')
					{
						$variables_description .= " = NULL";
					}
							
					$variables_description .= ",\n\t";				
				}	
			}
			
			//creating lists of fields, list of variables for update
			foreach ($data as $value)
			{
				if (($value[table_name] == $primary_table_name) && ($value[column_name] != $var_hull->deletingFieldName) && $value[primary_key] != true)
				{
					$fields_and_variables_list .= $value[column_name]." = "."@".$value[column_name].",\n\t";					
				}	
			}
			
			//removing last comma in the lists of variables and fields
			$variables_description =  trim(String::RemoveLastComma($variables_description));			
			$fields_and_variables_list =  trim(String::RemoveLastComma($fields_and_variables_list));		
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();

//making the whole string of procedure
$procedure_string =  <<<EOS_update
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	$variables_description
)
AS
BEGIN
UPDATE $primary_table_name
SET
	$fields_and_variables_list
WHERE
	$key_field[name] = @{$key_field[name]}
END

EOS_update;
			return $procedure_string;
		}	
		
		/**
		 * Generates stored procedure to delete data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureDelete($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_delete');		

			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);		
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();	

//making the whole string of procedure depending on existance of deleting field
			if ($has_deleted_field)
			{
							
$procedure_string =  <<<EOS_delete
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	@$key_field[name] $key_field[data_type]
)
AS
BEGIN
UPDATE $primary_table_name
SET
	$var_hull->deletingFieldName = 1
WHERE
	$key_field[name] = @{$key_field[name]}
END

EOS_delete;
			}
			else 
			{
$procedure_string =  <<<EOS_delete
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	@$key_field[name] $key_field[data_type]
)
AS
BEGIN
DELETE FROM $primary_table_name
WHERE
	$key_field[name] = @{$key_field[name]}
END

EOS_delete;
			}
			return $procedure_string;
		}		
						
		/**
		 * Generates stored procedure to get a row of data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureGet($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_get');		
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);	
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			if ($has_deleted_field)
			{
				$deleting_field_variables_string = "@".$var_hull->deletingFieldName." varchar = 0,";
				$deleting_field_where_string = "AND $primary_table_name.$var_hull->deletingFieldName = @$var_hull->deletingFieldName";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data);
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
//making the whole string of procedure
$procedure_string =  <<<EOS_get
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	$deleting_field_variables_string
	@$key_field[name] $key_field[data_type]
)
AS
BEGIN
SELECT 
	$fields_list
FROM
	$all_tables
WHERE
	$key_field[name] = @{$key_field[name]}
	$deleting_field_where_string
	$table_relations
END

EOS_get;
			return $procedure_string;
		}	
				
		/**
		 * Generates stored procedure to get a set of data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureList($data, &$procedure_name)		
		{
			$var_hull = new CodeGeneratorController();
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_list');		
			
			//seeking for sort_by field
			$sort_by_field = $this->FindSortByField($data, $key_field[name], $var_hull);
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);	
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			$WHERE = '';
			if ($has_deleted_field)
			{
				$deleting_field_variables_string = "@".$var_hull->deletingFieldName." varchar = 0,";
				$deleting_field_where_string = "AND $primary_table_name.$var_hull->deletingFieldName =' +@$var_hull->deletingFieldName+ '";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data);
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
			//check if we should use WHERE-clause
			$WHERE = 'WHERE 1=1 ';
			if (empty($deleting_field_where_string) && empty($table_relations))
				$WHERE = '';
				
			
//making the whole string of procedure
$procedure_string =  <<<EOS_list
-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	$deleting_field_variables_string
	@$var_hull->sortBy varchar(100) = '$primary_table_name.$sort_by_field',
	@$var_hull->sortMethod varchar(10) = 'ASC'
)
AS
BEGIN
DECLARE @SQL varchar(8000)

SET @SQL = 'SELECT
	$fields_list	 
FROM 
	$all_tables
$WHERE
    $deleting_field_where_string
	$table_relations
ORDER BY ' +@$var_hull->sortBy+ ' ' +@$var_hull->sortMethod+ ''

print 	@SQL
	EXEC(@SQL)
END

EOS_list;
			return $procedure_string;
		}	
				
		/**
		 * Generates stored procedure to get a set of data
		 *
		 * @param array $data necessary data
		 * @param string &$proc_name here we send name of the procedure
		 * @return string of the SQL-code
		 */
		function GenerateStoredProcedureListPaged($data, &$procedure_name)		
		{
			global $current_controller;
			
			//getting the table_name to sift out fields from other tables
			$primary_table_name = $this->FindTableName($data);
			
			if ($current_controller->tree_structure == true)
			{
				$parent_id_variable = "@parent_id varchar(100) ='0',";	
				$parent_id_declare = "DECLARE @PARENT_STRING varchar(8000)";
				$parent_id_condition = "if (@parent_id = '-1') SET @PARENT_STRING = ''
										else SET @PARENT_STRING = 'AND $primary_table_name.$current_controller->parentId IN ('+@parent_id+')';";
				$parent_string = "' + @PARENT_STRING + '";
			}
			else 
			{
				 $parent_id_variable = '';
				 $parent_id_declare = '';
				 $parent_id_condition = '';
				 $parent_string = '';
			}
			
			$var_hull = new CodeGeneratorController();			
			
			//finding key field
			$key_field = $this->FindKeyField($data);
			
			//getting the root of the procedure name from a table name
			$procedure_name = $this->FindProcedureName($var_hull->storedProcedurePrefix, $var_hull->tablePrefix, $primary_table_name, '_list_paged');		
			
			//seeking for sort_by field
			$sort_by_field = $this->FindSortByField($data, $key_field[name], $var_hull);
			
			//find out if there is field to mark deleted rows
			$has_deleted_field = $this->HasDeletedField($data, $var_hull);
			
			//find out all tables take part in procedure
			$all_tables = $this->AllTables($data);
			
			//constructing table relations
			$table_relations = $this->TablesRelations($data);
			
			//if we has deleting field, we should add it list of received variabels and WHERE-clause
			$deleting_field_variables_string = '';
			$deleting_field_where_string = '';
			if ($has_deleted_field)
			{
				$deleting_field_variables_string = "@".$var_hull->deletingFieldName." varchar = 0,";
				$deleting_field_where_string = "AND $primary_table_name.$var_hull->deletingFieldName = ' +@$var_hull->deletingFieldName+ '";
			}
			
			//creating description of variables, lists of fields, list of variables for insert
			$fields_list = $this->TablesFields($data, "\t");
			
			//string with info about our generator
			$generated_by = $this->GeneratedBy();
			
			//generating variabels description and conditions for filtering
			$variables_description = '';
			$declared_vars = '';
			$set_vars = '';
			$conditions = '';
			$using_vars = '';
			$TEMP = "_TEMP";
			foreach ($data as $value)
			{
				if ($value[column_name] != $var_hull->deletingFieldName)
				{
					//procedure parameters
					$variables_description .= "@{$value[column_name]} varchar(max)";					
					$variables_description .= " = ''";
					$variables_description .= ",\n\t";	
					
					//if we have parent_id than we separetely and manualy make conditions
					if ($current_controller->tree_structure)
						continue;
					
					//variables declaration
					$declared_vars .= "DECLARE @".strtoupper($value[column_name])."$TEMP"." varchar(max)";
					$declared_vars .= "\n\t";	
					
					//variables setting
					$set_vars .= "SET @".strtoupper($value[column_name])."$TEMP = ''";
					$set_vars .= "\n\t";	
					
					//conditions
					$conditions .= "if (@{$value[column_name]} != '') SET @".strtoupper($value[column_name])."$TEMP='";
					if (in_array($value[original_data_type], $this->stringDataTypes) || in_array($value[original_data_type], $this->textDataTypes))
					{						
						$conditions .= "\n\t AND $value[table_name].$value[column_name] LIKE ''%' +@$value[column_name]+ '%''';";
					}	
					else 
					{
						$conditions .= "\n\t AND $value[table_name].$value[column_name] = ' +@$value[column_name];";
					}	
					$conditions .= "\n\t";
					
					//conditions implementation
					$using_vars .= "' +@".strtoupper($value[column_name])."$TEMP+ '\n\t\t";
				}
			}
			
			//check if we should use WHERE-clause
			$WHERE = 'WHERE';
			if (empty($deleting_field_where_string) && empty($table_relations) && empty($using_vars))
				$WHERE = '';
			
			
			
//making the whole string of procedure
$procedure_string =  <<<EOS_list_paged


-- ================================================
-- $generated_by
-- ================================================
CREATE PROCEDURE [dbo].[$procedure_name]
(
	$deleting_field_variables_string
	$variables_description
	@$var_hull->sortBy varchar(100) = '$primary_table_name.$sort_by_field',
	@$var_hull->sortMethod varchar(10) = 'ASC',
	@$var_hull->pageOffset varchar(100) = 0,
	@$var_hull->pageNumber varchar(100) = 100000,
	@$var_hull->numRows BIGINT OUTPUT
	
)
AS
BEGIN

	DECLARE @SQL nvarchar(4000)
	$parent_id_declare	
	$parent_id_condition
	$declared_vars
	$set_vars
	$conditions

SET @SQL = '
	SET @temp_row_count = 
	(
		SELECT COUNT(*)		
		FROM $all_tables 
		$WHERE 
			1=1
			$deleting_field_where_string
			$table_relations
			$using_vars
			$parent_string
	)
	
	SELECT
		*
	FROM 
	(
	SELECT
		$fields_list,
		ROW_NUMBER() OVER(ORDER BY ' +@$var_hull->sortBy+ ' ' +@$var_hull->sortMethod+ ') AS RecID
	FROM 
		$all_tables
	$WHERE 
		1=1
		$deleting_field_where_string
		$table_relations
		$using_vars
		$parent_string
	) AS x
	WHERE
		RecID BETWEEN (' +@$var_hull->pageOffset+ '+1) AND (' +@$var_hull->pageOffset+ ' + '+@$var_hull->pageNumber+')
	'
	
print 	@SQL
EXECUTE sp_executesql @SQL, N'@temp_row_count bigint OUTPUT', @temp_row_count = @$var_hull->numRows OUTPUT
END


EOS_list_paged;
			return $procedure_string;
		}	
				
		/**
		 * Generates stored procedures
		 *
		 * @param array $data necessary data
		 * @return array $procedures generated procedures
		 */
		public function GenerateStoredProcedures($data)
		{
			$procedures = array
			(
				$proc_name => $this->GenerateStoredProcedureInsert($data, $proc_name), 
				$proc_name => $this->GenerateStoredProcedureUpdate($data, $proc_name), 
				$proc_name => $this->GenerateStoredProcedureDelete($data, $proc_name), 
				$proc_name => $this->GenerateStoredProcedureGet($data, $proc_name), 
				$proc_name => $this->GenerateStoredProcedureList($data, $proc_name), 
				$proc_name => $this->GenerateStoredProcedureListPaged($data, $proc_name)
			);
			return $procedures;
		}
		
		/**
		 * Creates stored procedures
		 *
		 * @param array $stored_procedures stored procedures
		 */
		public function CreateStoredProcedures(array $stored_procedures)
		{
			global $current_controller;
			foreach ($stored_procedures as $key=>$value)
			{
				
				$dropSQL = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$key]') AND type in (N'P')) DROP PROCEDURE [dbo].[$key]";
				
				//set ANSI_NULLS ON (because parameters for filtering often receive NULL)
				$current_controller->controller_model->Execute('', '', 'set ANSI_NULLS ON set QUOTED_IDENTIFIER ON');
				
				//erase previous procedures
				$current_controller->controller_model->Execute('', '', $dropSQL);

				//create new procedures
				$current_controller->controller_model->Execute('', '', $value);
			}
		}		
	
		
		/* SYSTEM TABLES AND COLUMNS INSERTION */
		
		/**
		 * Inserts all necessary data into system tables
		 *
		 * @param array $data data (post array) from the generator
		 */
		public function InsertSystemData($data)
		{
			$table_id = $this->InsertSystemTable($data);
			$this->InsertSystemTableColumns($table_id, $data[elements]);
		}
		
		/**
		 * Insert data inri t_system_tables
		 *
		 * @param string $table_name table name
		 * @return table id
		 */
		private function InsertSystemTable($data)
		{
			global $current_controller;
			
			$SQL = "
				DELETE FROM t_system_tables WHERE system_table_name = '$data[table_name]'
				INSERT INTO 
					t_system_tables (system_table_name, system_table_model_name, system_table_language_constant) 
				VALUES 
					('$data[table_name]', '".ucfirst($data[system_name])."', '$data[page_title]')	
				SELECT SCOPE_IDENTITY() as id
				";
			
			//insert system table
			$table = $current_controller->controller_model->Execute('', '', $SQL);
			return $table[0][id];
		}

		/**
		 * Inserts data into t_system_tables_columns
		 *
		 * @param unknown_type $table_id
		 * @param array $columns table columns
		 * @return result (true/false)
		 */
		private function InsertSystemTableColumns($table_id, array $columns)
		{
			global $current_controller;
			
			$SQL = '';
			
			foreach ($columns as $column)
			{
				if (empty($column[name])) continue;
				
				$SQL .= "
					INSERT INTO 
						t_system_tables_columns (system_table_id, system_table_column_name, system_table_column_constant)
					VALUES
						('$table_id', '$column[name]', '$column[title]')
					";
			}
			
			return $current_controller->controller_model->Execute('', '', $SQL);
		}
	}	
?>